<?php

namespace app\admin\controller\exam;

use app\common\controller\Backend;
use app\common\model\Student;
use app\common\model\Subject;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\cache\driver\Redis;
use think\Db;
use think\Exception;
use think\exception\PDOException;
use think\exception\ValidateException;

/**
 * 考试成绩
 *
 * @icon fa fa-circle-o
 */
class Exam extends Backend
{

    /**
     * Exam模型对象
     * @var \app\common\model\exam\Exam
     */
    protected $model = null;
    protected $noNeedRight = ['download'];
    protected $schoolLimit = true;


    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\common\model\exam\Exam;
    }

    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                ->where($where)
                ->order($sort, $order)
                ->count();

            $list = $this->model
                ->where($where)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            $list = collection($list)->toArray();
            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 添加
     */
    public function add()
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);

                if (!$params['subjects']) {
                    $this->error('请选择科目');
                }

                $subject_ids = array_keys($params['subjects']);
                $school_subject_ids = Subject::where('school_id', $params['school_id'])->whereIn('id', $subject_ids)->column('id');
                foreach ($params['subjects'] as $key => $subject) {
                    if ($key > 0 && !in_array($key, $school_subject_ids)) {
                        $this->error('科目「' . $subject['name'] . '」不存在');
                    }
                }

                $result = false;
                Db::startTrans();
                try {
                    //是否采用模型验证
                    if ($this->modelValidate) {
                        $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                        $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                        $this->model->validateFailException(true)->validate($validate);
                    }
                    list($params['start_time'], $params['end_time']) = explode(' - ', $params['time']);

                    $result = $this->model->allowField(true)->save($params);
                    $exam_id = $this->model->id;

                    $filePath = ROOT_PATH . 'public' . DS . $params['file'];
                    if (!is_file($filePath)) {
                        $this->error(__('No results were found'));
                    }
                    //实例化reader
                    $ext = pathinfo($filePath, PATHINFO_EXTENSION);
                    if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
                        $this->error(__('No results were found'));
                    }
                    if ($ext === 'csv') {
                        $file = fopen($filePath, 'r');
                        $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
                        $fp = fopen($filePath, "w");
                        $n = 0;
                        while ($line = fgets($file)) {
                            $line = rtrim($line, "\n\r\0");
                            $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                            if ($encoding != 'utf-8') {
                                $line = mb_convert_encoding($line, 'utf-8', $encoding);
                            }
                            if ($n == 0 || preg_match('/^".*"$/', $line)) {
                                fwrite($fp, $line . "\n");
                            } else {
                                fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                            }
                            $n++;
                        }
                        fclose($file) || fclose($fp);

                        $reader = new Csv();
                    } elseif ($ext === 'xls') {
                        $reader = new Xls();
                    } else {
                        $reader = new Xlsx();
                    }

                    //加载文件
                    $insert = [];

                    try {
                        if (!$PHPExcel = $reader->load($filePath)) {
                            $this->error(__('Unknown data format'));
                        }
                        $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
                        $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
                        $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
                        $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);//计算最大列数

                        $titleIndexStart = 2;
                        $titleIndexEnd = 2;

                        $fields = [];
                        for ($currentRow = $titleIndexStart; $currentRow <= $titleIndexEnd; $currentRow++) {
                            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                                $fields[] = $val;
                            }
                        }

                        $subjects = [];
                        foreach ($params['subjects'] as $key => $subject) {
                            $subjects[$subject['name']] = $subject;
                        }

                        $allSubjects = array_merge(array_keys($subjects), ['姓名', '学号']);

                        $less = array_diff($allSubjects, $fields);
                        if ($less) {
                            $less = implode(',', $less);
                            if ($less != '总分') {
                                $this->error('您选择的科目「' . $less . '」与您导入的数据中的科目不一致');
                            }
                        }
                        $more = array_diff($fields, $allSubjects);
                        if ($more) {
                            $more = implode(',', $more);
                            if ($more != '总分') {
                                $this->error('您导入的数据中的科目「' . $more . '」与您选择的科目不一致');
                            }
                        }

                        for ($currentRow = $titleIndexEnd + 1; $currentRow <= $allRow; $currentRow++) {
                            $values = [];
                            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                                $values[] = is_null($val) ? '' : $val;
                            }

                            $temp = array_combine($fields, $values);

                            $row = [];
                            foreach ($temp as $k => $v) {
                                if ($k !== '' && $v) {
                                    if ($k == '学号') {
                                        $row['student_number'] = $v;
                                    } else if (isset($subjects[$k]) && $subjects[$k]['id'] != '0') {
                                        if (!isset($row['score'])) {
                                            $row['score'] = 0;
                                        }
                                        $row['score'] += $v;

                                        $row['json'][$subjects[$k]['id']] = [
                                            'id' => $subjects[$k]['id'],
                                            'name' => $subjects[$k]['name'],
                                            'score' => $v
                                        ];
                                    }
                                }
                            }

                            if ($row) {
                                if (isset($insert[$row['student_number']])) {
                                    // 学号重复
                                    $this->error('学号「' . $row['student_number'] . '」重复');
                                } else {
                                    $row['exam_id'] = $exam_id;
                                    $insert[$row['student_number']] = $row;
                                }
                            }
                        }

                        //dd($insert);

                        if (!$insert) {
                            $this->error(__('No rows were updated'));
                        }

                        $all_classes = [];

                        // 判断是否有匹配不到学生的学号
                        $students = Student::alias('student')
                            ->join('__CLASSES__ classes', 'classes.id = student.class_id')
                            ->join('__GRADE__ grade', 'grade.id = student.grade_id')
                            ->whereIn('student.number', array_column($insert, 'student_number'))
                            ->where('student.school_id', $params['school_id'])
                            ->where('student.grade_id', $params['grade_id'])
                            ->column('student.id,student.name,student.school_id,student.grade_id,grade.name grade_name,student.class_id,classes.name class_name', 'number');
                        foreach ($insert as $key => $value) {
                            if (isset($students[$value['student_number']])) {
                                $insert[$key]['student_id'] = $students[$value['student_number']]['id'];
                                $insert[$key]['student_name'] = $students[$value['student_number']]['name'];
                                $insert[$key]['student_number'] = $value['student_number'];
                                $insert[$key]['school_id'] = $students[$value['student_number']]['school_id'];
                                $insert[$key]['grade_id'] = $students[$value['student_number']]['grade_id'];
                                $insert[$key]['class_id'] = $students[$value['student_number']]['class_id'];

                                if (!isset($all_classes[$students[$value['student_number']]['class_id']])) {
                                    $all_classes[$students[$value['student_number']]['class_id']] = [
                                        'id' => $students[$value['student_number']]['class_id'],
                                        'name' => $students[$value['student_number']]['class_name'],
                                        'grade_id' => $students[$value['student_number']]['grade_id'],
                                        'grade_name' => $students[$value['student_number']]['grade_name'],
                                        'school_id' => $students[$value['student_number']]['school_id'],
                                    ];
                                }
                            } else {
                                unset($insert[$key]);
                                //$this->error('学号「' . $value['student_number'] . '」没有找到匹配的学生');
                            }
                        }

                        if (!$insert) {
                            $this->error(__('No rows were updated'));
                        }

                        //$data = compact('exam_id', 'insert', 'subjects');
                        //\think\Hook::listen("exam_init", $data);

                        $school_sort = []; //全校总分排名
                        $school_subject_sort = []; //全校科目排名
                        $classes_sort = []; //班级总分排名
                        $classes_subject_sort = []; //班级科目排名

                        $report_classes_sort = [];//报告班级排名
                        $report_classes_subject_sort = [];//报告班级科目排名
                        $classes_student_total_count = [];//班级总学生数
                        $classes_student_excellent_count = [];//班级优秀学生数
                        $classes_student_pass_count = [];//班级及格学生数
                        $classes_student_subject_excellent_count = [];//班级科目优秀学生数
                        $classes_student_subject_pass_count = [];//班级科目及格学生数

//                        $redis = new \Redis;
//                        $redis->connect('127.0.0.1');
//                        $rds_report_classes = 'report_' . $exam_id;
//                        $redis->zRem($rds_report_classes);
//                        $redis->zAdd($rds_report_classes, 1, 2);
//                        $redis->zAdd($rds_report_classes, 1, 3);
//                        $report_classes_sort2 = $redis->zRevRange($rds_report_classes, 0, -1, ['withscores' => true]);
//                        $redis->zIncrBy($rds_report_classes, $value['score'], $value['class_id']);

                        foreach ($insert as $key => $value) {
                            // 全校总分排名
                            $school_sort[] = [
                                'id' => $value['student_id'],
                                'score' => $value['score']
                            ];
                            // 班级总分排名
                            $classes_sort[$value['class_id']][] = [
                                'id' => $value['student_id'],
                                'score' => $value['score']
                            ];

                            // 班级总学生数
                            if (!isset($classes_student_total_count[$value['class_id']])) {
                                $classes_student_total_count[$value['class_id']] = 0;
                            }
                            $classes_student_total_count[$value['class_id']]++;

                            // 班级学生总分优秀、及格数
                            if (!isset($classes_student_excellent_count[$value['class_id']])) {
                                $classes_student_excellent_count[$value['class_id']] = 0;
                            }
                            if (!isset($classes_student_pass_count[$value['class_id']])) {
                                $classes_student_pass_count[$value['class_id']] = 0;
                            }
                            if (isset($params['subjects'][0])) {
                                $subject = $params['subjects'][0];
                                if ($value['score'] >= $subject['excellent_score']) {
                                    //优秀
                                    $classes_student_excellent_count[$value['class_id']]++;
                                }
                                if ($value['score'] >= $subject['pass_score']) {
                                    //及格
                                    $classes_student_pass_count[$value['class_id']]++;
                                }
                            }

                            // 班级总分
                            if (!isset($report_classes_sort[$value['class_id']])) {
                                $report_classes_sort[$value['class_id']] = 0;
                            }
                            $report_classes_sort[$value['class_id']] += $value['score'];

                            // 循环学生成绩
                            foreach ($value['json'] as $k => $val) {
                                // 全校科目排名
                                $school_subject_sort[$val['id']][] = [
                                    'id' => $value['student_id'],
                                    'score' => $val['score']
                                ];
                                // 班级科目排名
                                $classes_subject_sort[$value['class_id']][$val['id']][] = [
                                    'id' => $value['student_id'],
                                    'score' => $val['score']
                                ];

                                // 班级科目学生总分优秀、及格数
                                if (!isset($classes_student_subject_excellent_count[$value['class_id']])) {
                                    $classes_student_subject_excellent_count[$value['class_id']] = [];
                                }
                                if (!isset($classes_student_subject_excellent_count[$value['class_id']][$val['id']])) {
                                    $classes_student_subject_excellent_count[$value['class_id']][$val['id']] = 0;
                                }
                                if (!isset($classes_student_subject_pass_count[$value['class_id']])) {
                                    $classes_student_subject_pass_count[$value['class_id']] = [];
                                }
                                if (!isset($classes_student_subject_pass_count[$value['class_id']][$val['id']])) {
                                    $classes_student_subject_pass_count[$value['class_id']][$val['id']] = 0;
                                }
                                if (isset($params['subjects'][$val['id']])) {
                                    $subject = $params['subjects'][$val['id']];
                                    if ($val['score'] >= $subject['excellent_score']) {
                                        //优秀
                                        $classes_student_subject_excellent_count[$value['class_id']][$val['id']]++;
                                    }
                                    if ($val['score'] >= $subject['pass_score']) {
                                        //及格
                                        $classes_student_subject_pass_count[$value['class_id']][$val['id']]++;
                                    }
                                }

                                // 报告班级科目排名
                                if (!isset($report_classes_subject_sort[$val['id']])) {
                                    $report_classes_subject_sort[$val['id']] = [];
                                }
                                if (!isset($report_classes_subject_sort[$val['id']][$value['class_id']])) {
                                    $report_classes_subject_sort[$val['id']][$value['class_id']] = 0;
                                }
                                $report_classes_subject_sort[$val['id']][$value['class_id']] += $val['score'];

                            }

                        }

                        // 全校总分排名
                        array_multisort(array_column($school_sort, 'score'), SORT_DESC, $school_sort);
                        foreach ($school_sort as $key => $value) {
                            if (0 == $key) {
                                $school_sort[$key]['sort'] = 1;
                            } else if ($school_sort[$key]['score'] == $school_sort[$key - 1]['score']) {
                                $school_sort[$key]['sort'] = $school_sort[$key - 1]['sort'];
                            } else {
                                $school_sort[$key]['sort'] = $key + 1;
                            }
                        }
                        $school_sorted = array_column($school_sort, 'sort', 'id');

                        // 全校科目排名
                        foreach ($school_subject_sort as $key => $value) {
                            array_multisort(array_column($value, 'score'), SORT_DESC, $school_subject_sort[$key]);
                        }
                        foreach ($school_subject_sort as $key => $value) {
                            foreach ($value as $k => $val) {
                                if (0 == $k) {
                                    $school_subject_sort[$key][$k]['sort'] = 1;
                                } else if ($school_subject_sort[$key][$k]['score'] == $school_subject_sort[$key][$k - 1]['score']) {
                                    $school_subject_sort[$key][$k]['sort'] = $school_subject_sort[$key][$k - 1]['sort'];
                                } else {
                                    $school_subject_sort[$key][$k]['sort'] = $k + 1;
                                }
                            }
                        }
                        $school_subject_sorted = [];
                        foreach ($school_subject_sort as $key => $value) {
                            $school_subject_sorted[$key] = array_column($value, 'sort', 'id');
                        }

                        // 班级总分排名
                        foreach ($classes_sort as $key => $value) {
                            array_multisort(array_column($value, 'score'), SORT_DESC, $classes_sort[$key]);
                        }
                        foreach ($classes_sort as $key => $value) {
                            foreach ($value as $k => $val) {
                                if (0 == $k) {
                                    $classes_sort[$key][$k]['sort'] = 1;
                                } else if ($classes_sort[$key][$k]['score'] == $classes_sort[$key][$k - 1]['score']) {
                                    $classes_sort[$key][$k]['sort'] = $classes_sort[$key][$k - 1]['sort'];
                                } else {
                                    $classes_sort[$key][$k]['sort'] = $k + 1;
                                }
                            }
                        }
                        $classes_sorted = [];
                        foreach ($classes_sort as $key => $value) {
                            $classes_sorted[$key] = array_column($value, 'sort', 'id');
                        }

                        //echo json_encode($classes_sorted);die;

                        // 班级科目排名
                        foreach ($classes_subject_sort as $key => $value) {
                            foreach ($value as $k => $val) {
                                array_multisort(array_column($val, 'score'), SORT_DESC, $classes_subject_sort[$key][$k]);
                            }
                        }
                        foreach ($classes_subject_sort as $key => $value) {
                            foreach ($value as $index => $item) {
                                foreach ($item as $k => $val) {
                                    if (0 == $k) {
                                        $classes_subject_sort[$key][$index][$k]['sort'] = 1;
                                    } else if ($classes_subject_sort[$key][$index][$k]['score'] == $classes_subject_sort[$key][$index][$k - 1]['score']) {
                                        $classes_subject_sort[$key][$index][$k]['sort'] = $classes_subject_sort[$key][$index][$k - 1]['sort'];
                                    } else {
                                        $classes_subject_sort[$key][$index][$k]['sort'] = $k + 1;
                                    }
                                }
                            }
                        }
                        $classes_subject_sorted = [];
                        foreach ($classes_subject_sort as $key => $value) {
                            foreach ($value as $k => $val) {
                                $classes_subject_sorted[$key][$k] = array_column($val, 'sort', 'id');
                            }
                        }

                        //echo json_encode(compact('school_sort', 'school_subject_sort', 'classes_sort', 'classes_subject_sort'));die;
                        //echo json_encode(compact('school_sorted', 'school_subject_sorted', 'classes_sorted', 'classes_subject_sorted'));die;

                        foreach ($insert as $key => &$value) {
                            if (isset($school_sorted[$value['student_id']])) {
                                $value['school_sort'] = $school_sorted[$value['student_id']];
                            }
                            if (isset($classes_sorted[$value['class_id']][$value['student_id']])) {
                                $value['classes_sort'] = $classes_sorted[$value['class_id']][$value['student_id']];
                            }
                            foreach ($value['json'] as $k => &$val) {
                                if (isset($school_subject_sorted[$val['id']][$value['student_id']])) {
                                    $val['school_sort'] = $school_subject_sorted[$val['id']][$value['student_id']];
                                }
                                if (isset($classes_subject_sorted[$value['class_id']][$val['id']][$value['student_id']])) {
                                    $val['classes_sort'] = $classes_subject_sorted[$value['class_id']][$val['id']][$value['student_id']];
                                }
                            }
                        }

                        //echo json_encode($insert);die;

                        // 如果数据量大 分批保存
                        $limit = 100;
                        $count = ceil(count($insert) / $limit);
                        $student = new \app\common\model\exam\Student();
                        for ($i = 0; $i < $count; $i++) {
                            $student->isUpdate(false)->saveAll(array_slice($insert, $i * $limit, $limit));
                        }

                        //报告班级科目排名
                        $report_classes_subject_sorted = [];
                        foreach ($report_classes_subject_sort as $k => $val) {
                            arsort($val);
                            $sort = 1;
                            $last_item = [];
                            foreach ($val as $key => $value) {
                                if (!isset($report_classes_subject_sorted[$key])) {
                                    $report_classes_subject_sorted[$key] = [];
                                }
                                if (!isset($report_classes_subject_sorted[$key][$k])) {
                                    $report_classes_subject_sorted[$key][$k] = [];
                                }
                                $report_classes_subject_sorted[$key][$k]['id'] = $k;
                                $report_classes_subject_sorted[$key][$k]['name'] = $params['subjects'][$k]['name'];
                                $report_classes_subject_sorted[$key][$k]['total_score'] = $value;
                                $report_classes_subject_sorted[$key][$k]['average_score'] = $value / $classes_student_total_count[$key];
                                $report_classes_subject_sorted[$key][$k]['excellent_rate'] = $classes_student_subject_excellent_count[$key][$k] / $classes_student_total_count[$key] * 100;
                                $report_classes_subject_sorted[$key][$k]['pass_rate'] = $classes_student_subject_pass_count[$key][$k] / $classes_student_total_count[$key] * 100;
                                if (1 == $sort) {
                                    $report_classes_subject_sorted[$key][$k]['sort'] = $sort;
                                } else if ($last_item && $last_item['score'] == $value) {
                                    $report_classes_subject_sorted[$key][$k]['sort'] = $last_item['sort'];
                                } else {
                                    $report_classes_subject_sorted[$key][$k]['sort'] = $sort;
                                    $last_item = ['score' => $value, 'sort' => $sort];
                                }
                                $sort++;
                            }
                        }
                        //报告班级排名
                        $report_classes_sorted = [];
                        arsort($report_classes_sort);
                        $sort = 1;
                        $last_item = [];
                        foreach ($report_classes_sort as $key => $value) {
                            if (!isset($report_classes_sorted[$key])) {
                                $report_classes_sorted[$key] = [];
                            }

                            if (isset($all_classes[$key])) {
                                $report_classes_sorted[$key]['class_name'] = $all_classes[$key]['name'];
                                $report_classes_sorted[$key]['grade_id'] = $all_classes[$key]['grade_id'];
                                $report_classes_sorted[$key]['grade_name'] = $all_classes[$key]['grade_name'];
                            }
                            $report_classes_sorted[$key]['exam_id'] = $exam_id;
                            $report_classes_sorted[$key]['class_id'] = $key;
                            $report_classes_sorted[$key]['total_score'] = $value;
                            $report_classes_sorted[$key]['average_score'] = $value / $classes_student_total_count[$key];
                            $report_classes_sorted[$key]['excellent_rate'] = $classes_student_excellent_count[$key] / $classes_student_total_count[$key]  * 100;
                            $report_classes_sorted[$key]['pass_rate'] = $classes_student_pass_count[$key] / $classes_student_total_count[$key] * 100;
                            if (1 == $sort) {
                                $report_classes_sorted[$key]['sort'] = $sort;
                            } else if ($last_item && $last_item['score'] == $value) {
                                $report_classes_sorted[$key]['sort'] = $last_item['sort'];
                            } else {
                                $report_classes_sorted[$key]['sort'] = $sort;
                                $last_item = ['score' => $value, 'sort' => $sort];
                            }

                            if (isset($report_classes_subject_sorted[$key])) {
                                $report_classes_sorted[$key]['json'] = $report_classes_subject_sorted[$key];
                            }
                            $sort++;
                        }

                        //echo json_encode($report_classes_sorted);die;
                        $report = new \app\common\model\exam\Report();
                        $report->saveAll(array_values($report_classes_sorted));

                        // 更新成绩数据
                        \app\common\model\exam\Exam::where('id', $exam_id)->update([
                            'import_complete' => \app\common\model\exam\Exam::COMPLETED,
                            'classes_count' => count($all_classes),
                            'subject_count' => count($subjects) - 1,
                            'student_count' => count($insert),
                        ]);





                    } catch (Exception $exception) {
                        $this->error($exception->getMessage());
                    }


                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {

                    $this->success();
                } else {
                    $this->error(__('No rows were inserted'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        return $this->view->fetch();
    }

    /**
     * 下载模板
     *
     * @User ZhenZiHan
     * @Date 2020/4/29
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function download()
    {
        $school_id = $this->request->request('school_id');
        $subjects = $this->request->request('subjects');
        $subjects = explode(',', $subjects);

        $subjects = Subject::where('school_id', $school_id)->whereIn('id', $subjects)->field('id,name')->select();

        if ($subjects) {

            $subjects = array_merge(['姓名', '学号'], array_column($subjects, 'name'));

            set_time_limit(0);
            $filePath = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'template/学生导入名单详情.xlsx';
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            //$worksheet->setTitle('模板测试标题');
            $columns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W'];

            $worksheet->mergeCells('A1:' . $columns[count($subjects) - 1] . '1');
            $worksheet->getCell("A1")->setValue('学生导入名单详情');
            $worksheet->getCell("A1")->getStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $worksheet->getCell("A1")->getStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            $worksheet->getCell("A1")->getStyle()->getFont()->setBold(true);

            foreach ($subjects as $key => $subject) {
                $worksheet->getCell($columns[$key] . "2")->setValue($subject);
            }

            \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');

            //下载文档
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="学生导入名单详情.xlsx"');
            header('Cache-Control: max-age=0');
            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
            $writer->save('php://output');

            return;

        } else {
            echo '错误';
            die;
        }


    }
}
